Open topic with navigation

iLoADER

 HOME

Data Issues and Checklist

Here is a checklist of issues which might slow the data import.

Introduction

Data may be imported into an Engine database in a number of ways. The most common method is to use iLoader to import data provided as fixed length or delimited files. ODBC is also available and iLoader may be extended with other Connectivity options in future.

Importing may proceed easily but often issues arise that make the task more difficult on the first attempt.

Some of the issues that may arise from supplied data include:

  • Variable length records
  • Corrupted Data
  • Data not matching definition
  • Insufficient or no data definition information
  • Missing Delimiters or EOR characters
  • Missing Fields
  • Files too large to manipulate or examine with standard MS tools.

Another less obvious issue is the provision of Field Names in electronic form. As the field and table names will appear in the User Interface, the actual format may not be in the most presentable format.

The purpose of this document is to help speed the import process by providing a checklist of the deliverables expected.

Samples - Data Definition

Table Name: Account
Field name Data Type Size Description
Unique identification number Text 15 Primary Key (Business Table)
Accounting period Integer 2 Number of Weeks covered by accounts
Consolidated accounts Text 1 "Y" or "N"
Date of accounts Date 8 CCYYMMDD
Net Profit Real

1.2

 ±9999999999.99 (14 digits including sign - decimal point implicit)

Raw Data Statistics

Raw Data File name Account.txt
File size 120000 bytes
File Format Fixed record length
Record Length 100
End of Record Character(s) CRLF
Field Delimiter Not Applicable
Media CD

Import Data Deliverable Checklist

  • Raw Data in Fixed Format or Delimited Format. One file per table. Data must be ASCII text (i.e. not packed decimals). Alternatively data may be supplied in a format that can be read by the GDT importers.
  • Statement of Raw Data file name(s), file size(s), media, number of units.
  • Statement of Record length, End of Record characters, or field delimiter (and Qualifiers) depending on format.
  • Data definition contain explanation of raw data provided including: Table name Field name Data type Field size
  • Description including format, domain values, ranges, precision, handling of NULL values, default values, etc. Identification of Primary and Foreign keys Preferred format csv or Excel
  • Outline known data issues such as duplicate records/keys
  • Sample data should be provided for a couple of records from each table together with field names to allow easy cross checking. Preferred format csv or Excel
  • All Files supplied electronically.
  • Delivery Document outlining all items supplied including versions and changes.
  • Data Security requirements. Outline of any special requirements such as disposal or return of data.
Note:

A quick check of the raw data file is to apply the following formula:

Number of Records = File size/ (Record Length + 2 )

This calculation should yield a positive integer (whole number).

  Online & Instructor-Led Courses | Training Videos | Webinar Recordings
© Alterian. All Rights Reserved. | Privacy Policy | Legal Notice